package br.com.appclassificados.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import br.com.appclassificados.helpers.ConnectionFactory;
import br.com.appclassificados.model.CatMarca;

public class CatMarcaDao implements CRUD<CatMarca> {
	
	// TODO ATRIBUTOS
	private Connection con;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
		
	// TODO Construtores
	public CatMarcaDao(){
		super();
		this.con = ConnectionFactory.getConnection().getConn();
	}
	
	//Testado com Exito, 23:30, 19/10/2013, Eduardo C.
	@Override
	public boolean insert(CatMarca obj) {
		try{
            PreparedStatement ps = this.con.prepareStatement("insert into cat_marca values(?, ?, ?)");
            ps.setInt(1, getChave());
            ps.setInt(2, obj.getMarcaId());
            ps.setInt(3, obj.getSub_categoriaId());
            ps.execute();
            ps.close();
            this.con.close();                         
            return true;            
        }catch(Exception e){
            return false;
        }   
	}
	
	//Testado com Exito, 23:30, 19/10/2013, Eduardo C.
	@Override
	public boolean delete(CatMarca obj) {
		try{
			PreparedStatement ps = this.con.prepareStatement("delete from cat_marca where cat_marcaid = ?");
			ps.setInt(1, obj.getCat_marcaId());
			ps.executeUpdate();
			ps.close();
			this.con.close();                         
			return true;            
		}catch(Exception e){
			return false;
		} 
	}

	//Testado com Exito, 23:30, 19/10/2013, Eduardo C.
	@Override
	public boolean update(CatMarca obj) {
		try{
            PreparedStatement ps = this.con.prepareStatement("UPDATE cat_marca set marcaid = ?," +
            		"sub_categoriaid = ? where cat_marcaid = ?");            
            
            ps.setInt(1, obj.getMarcaId());
            ps.setInt(2, obj.getSub_categoriaId());
            ps.setInt(3, obj.getCat_marcaId());
            ps.executeUpdate();
            ps.close();
            this.con.close();                         
            return true;            
        }catch(Exception e){
            return false;
        }
	}

	//Testado com Exito, 23:30, 19/10/2013, Eduardo C.
	@Override
	public List<CatMarca> readList() {
		try{
            PreparedStatement ps = this.con.prepareStatement("select cat_marcaid, " + 
		"marcaid, sub_categoriaid from cat_marca");                                    
            this.rs = ps.executeQuery();            
            
            List<CatMarca> lm = new ArrayList<CatMarca>();
            
            while(this.rs.next()){
                CatMarca c = new CatMarca(  this.rs.getInt("cat_marcaid"),
                							this.rs.getInt("marcaid"),
                							this.rs.getInt("sub_categoriaid"));
                lm.add(c);                
            }
            return lm;
        }catch(Exception e){
            return null;
        }
	}

	//Testado com Exito, 23:30, 19/10/2013, Eduardo C.
	@Override
	public CatMarca search(int id) {
		
		try{
            PreparedStatement ps = this.con.prepareStatement("select marcaid, sub_categoriaid " +
            		"from cat_marca where cat_marcaid = ?");                        
            ps.setInt(1, id);
            this.rs = ps.executeQuery();
            
            if(this.rs.next()){
                CatMarca c = new CatMarca(id, this.rs.getInt("marcaid"),
                							  this.rs.getInt("sub_categoriaid"));
                return c;
            }else
                return null;
        }catch(Exception e){
            return null;
        }
		
	}

	//Verificado com Exito, 15:06, 20/10/2013, Maicon D.
	//Criado com a finalidade de cadastrar um anuncio,
	//baseado na marca e subCategoria retorna o cat_marcaId
	public CatMarca search(int marcaId, int subCategoriaId) {
		
		try{
            PreparedStatement ps = this.con.prepareStatement("select cat_marcaid " +
            		"from cat_marca where marcaid = ? and sub_categoriaid = ?");                        
            ps.setInt(1, marcaId);
            ps.setInt(2, subCategoriaId);
            this.rs = ps.executeQuery();
            
            if(this.rs.next()){
                CatMarca c = new CatMarca(this.rs.getInt("cat_marcaid"), marcaId, subCategoriaId);
                return c;
            }else
                return null;
        }catch(Exception e){
            return null;
        }
		
	}
	
	//Testado com Exito, 23:30, 19/10/2013, Eduardo C.
	@Override
	public int getChave() {
		try{
	         PreparedStatement ps = this.con.prepareStatement("select max(cat_marcaid) " +
	        		 											"from cat_marca");                        
	         this.rs = ps.executeQuery();
	            
	         if(this.rs.next()){
	        	 return (this.rs.getInt("max(cat_marcaid)") + 1);
	         }else
	        	 return 0;
	     }catch(Exception e){
	    	 return 0;
	     }
	}

	@Override
	public CatMarca search(String value) {
		// TODO Auto-generated method stub
		return null;
	}

}//fim[CatMarcaDao]
